﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ISS.IR.RRNMS.Library;
using ISS.IR.RRNMS.Library.BusinessLogic;
using System.Text;
using System.Data;
using System.IO;

namespace ISS.IR.RRNMS.Web
{
    public partial class QueryBuilderPage : System.Web.UI.Page
    {
        #region Event Handlers

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindEntities();
                LoadEntityFields();
                BindEntityFields();
                LoadOperators();
            }



        }

        protected void btnAddCondition_Click(object sender, EventArgs e)
        {
            AddConditionToGrid();
        }

        protected void ddlEntities_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindEntityFields();
            grdConditions.DataSource = null;
            grdConditions.DataBind();
            Session["QueryConditions"] = null;

            gvAccounts.DataSource = null;
            gvAccounts.DataBind();

            gvAppointment.DataSource = null;
            gvAppointment.DataBind();

            BindSearchViews();
        }

        protected void ddlFields_SelectedIndexChanged(object sender, EventArgs e)
        {
            BindOperators();
            HandleLookupField();
        }

        protected void btnSearch_Click(object sender, ImageClickEventArgs e)
        {
            try
            {
                if (Session["QueryConditions"] != null)
                {
                    List<QueryCondition> lstConditions;
                    lstConditions = (List<QueryCondition>)Session["QueryConditions"];

                    StringBuilder queryText = new StringBuilder();
                    QueryBuilder queyBuilder = new QueryBuilder();


                    if (ddlEntities.SelectedItem.Text == "اللاجئين")
                        queryText.Append("SELECT AccountId, FullName, UNHCR FROM Accounts");
                    else if (ddlEntities.SelectedItem.Text == "المواعيد")
                        queryText.Append("SELECT AccountName, AccountId, UserName , RoomName , SlotName , Date  FROM View_Calendar");
                    else
                        return;


                    queryText.Append(" WHERE ");


                    foreach (QueryCondition condtion in lstConditions)
                    {

                        queryText.Append(queyBuilder.GetQueryFromCondition(condtion) + " AND ");
                    }

                    queryText.Remove(queryText.ToString().LastIndexOf("AND"), 4);

                    QueryBuilder queryBuilder = new QueryBuilder();
                    DataTable dtSearchResult = queryBuilder.GetSearchResult(queryText.ToString());

                    gvAccounts.Visible = false;
                    gvAppointment.Visible = false;

                    if (ddlEntities.SelectedItem.Text == "المواعيد")
                    {
                        gvAppointment.Visible = true;
                        gvAppointment.DataSource = dtSearchResult;
                        gvAppointment.DataBind();
                    }
                    else if (ddlEntities.SelectedItem.Text == "اللاجئين")
                    {
                        gvAccounts.Visible = true;
                        gvAccounts.DataSource = dtSearchResult;
                        gvAccounts.DataBind();
                    }


                    Session["dtSearchResult"] = dtSearchResult;
                }
            }
            catch (Exception exc)
            {

            }
        }

        protected void grdConditions_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName == "DeleteQueryCondition")
            {
                int rowIndex = Convert.ToInt32(e.CommandArgument);

                List<QueryCondition> lstConditions = (List<QueryCondition>)Session["QueryConditions"];
                lstConditions.RemoveAt(rowIndex);
                Session["QueryConditions"] = lstConditions;
                grdConditions.DataSource = lstConditions;
                grdConditions.DataBind();

            }
        }

        #endregion

        #region private methods

        private void BindEntities()
        {
            QueryBuilder queryBuiler = new QueryBuilder();

            // Bind Entities DropDownList
            List<Item> lstEntities = queryBuiler.GetCategoryItems(1);
            if (lstEntities != null)
            {
                Item emptyEntity = new Item { Value = "أختر ...", Id = 0 };

                if (!lstEntities.Contains(emptyEntity))
                    lstEntities.Insert(0, emptyEntity);

                // Removing Payments and Users
                lstEntities.Remove(new Item { Id = 7, Value = "المستخدمين" });
                lstEntities.Remove(new Item { Id = 10, Value = "المدفوعات" });

                ddlEntities.DataSource = lstEntities;
                ddlEntities.DataTextField = "Value";
                ddlEntities.DataValueField = "Id";
                ddlEntities.DataBind();
            }
            else
                ddlEntities.Items.Clear();
        }

        private void BindEntityFields()
        {
            if (ddlEntities.SelectedValue == "" || Convert.ToInt32(ddlEntities.SelectedValue) == 0)
            {
                ddlFields.Items.Clear();
                return;
            }

            List<EntityField> lstFields;

            QueryBuilder queryBuilder = new QueryBuilder();
            lstFields = queryBuilder.GetEntityFields(Convert.ToInt32(ddlEntities.SelectedValue));

            if (lstFields != null)
            {
                EntityField emtptField = new EntityField { FieldDisplayName = "أختر ...", FieldId = 0 };
                if (!lstFields.Contains(emtptField))
                    lstFields.Insert(0, emtptField);
                ddlFields.DataSource = lstFields;
                ddlFields.DataTextField = "FieldDisplayName";
                ddlFields.DataValueField = "FieldId";
                ddlFields.DataBind();

                Session["EntityFields"] = lstFields;
            }
            else
                ddlFields.Items.Clear();
        }

        private void BindOperators()
        {
            try
            {
                int fieldId = Convert.ToInt32(ddlFields.SelectedValue);

                if (fieldId == 0)
                {
                    ddlOperators.Items.Clear();
                    return;
                }
                // If session object doesn't contain Entity Fields
                if (Session["EntityFields"] == null)
                    LoadEntityFields();

                if (Session["EntityFields"] != null)
                {
                    List<EntityField> fields = (List<EntityField>)Session["EntityFields"];
                    if (fields.Count > 0)
                    {
                        EntityField currentField = fields.Find(delegate(EntityField f) { return f.FieldId == fieldId; });
                        int fieldTypeId = ((Item)currentField.Type).Id;

                        if (Session["Operators"] == null)
                            LoadOperators();

                        List<Operator> allOperators = (List<Operator>)Session["Operators"];
                        List<Operator> currentFieldOperators = allOperators.FindAll(delegate(Operator op) { return op.TypeId == fieldTypeId; });

                        if (currentFieldOperators != null && currentFieldOperators.Count > 0)
                        {
                            Operator emptyOperator = new Operator { OperatorDisplayName = "أختر ...", OperatorSchemaName = "", TypeId = 0 };
                            //if (!currentFieldOperators.Contains(emptyOperator) && currentField.Type.Id != 15)
                            currentFieldOperators.Insert(0, emptyOperator);
                            ddlOperators.DataSource = currentFieldOperators;
                            ddlOperators.DataTextField = "OperatorDisplayName";
                            ddlOperators.DataValueField = "OperatorSchemaName";
                            ddlOperators.DataBind();
                        }
                        else
                        {
                            ddlOperators.Items.Clear();
                        }

                    }
                    else
                        ddlOperators.Items.Clear();
                }
                else
                    ddlOperators.Items.Clear();
            }
            catch (Exception exc)
            {
                ddlOperators.Items.Clear();
            }
        }

        private void LoadOperators()
        {
            try
            {
                QueryBuilder queryBuilder = new QueryBuilder();
                List<Operator> lstOperators = queryBuilder.GetAllOperators();
                if (lstOperators != null)
                    Session["Operators"] = lstOperators;
            }
            catch (Exception exc)
            {
            }

        }

        private void LoadEntityFields()
        {
            try
            {
                QueryBuilder queryBuilder = new QueryBuilder();
                List<EntityField> lstFields = queryBuilder.GetEntityFields(Convert.ToInt32(ddlEntities.SelectedValue));
                if (lstFields != null)
                    Session["EntityFields"] = lstFields;
            }
            catch (Exception exc)
            {

            }
        }

        private void AddConditionToGrid()
        {
            int fieldId = Convert.ToInt32(ddlFields.SelectedValue);
            //int operatorId = Convert.ToInt32(ddlOperators.SelectedValue);

            if (fieldId == 0)
            {
                ddlOperators.Items.Clear();
                return;
            }

            QueryCondition condition = new QueryCondition();

            // If session object doesn't contain Entity Fields
            if (Session["EntityFields"] == null)
                LoadEntityFields();

            if (Session["EntityFields"] != null)
            {
                List<EntityField> fields = (List<EntityField>)Session["EntityFields"];
                if (fields.Count > 0)
                {
                    EntityField currentField = fields.Find(delegate(EntityField f) { return f.FieldId == fieldId; });
                    condition.FieldId = fieldId;
                    condition.FieldDisplayName = currentField.FieldDisplayName;
                    condition.FieldSchemaName = currentField.FieldSchemaName;
                    condition.FieldType = currentField.Type;

                    if (currentField.FieldSchemaName == "AccountId" ||
                        currentField.FieldSchemaName == "UserId" ||
                        currentField.FieldSchemaName == "SlotId")
                    {
                        condition.Value = ddlLookupValue.SelectedItem.Text;
                        condition.LookupGuidValue = new Guid(ddlLookupValue.SelectedValue);
                    }
                    else if (currentField.Type.Id == 12 || currentField.Type.Id == 13) // string or Numeric
                        condition.Value = txtValue.Text;
                    else if (currentField.Type.Id == 14) // Date
                        if (txtDate.Text != "")
                            condition.Value = txtDate.Text;
                        else
                            condition.Value = hdnDateValue.Value;

                    else
                    {
                        condition.Value = ddlLookupValue.SelectedItem.Text;
                        condition.LookupValue = Convert.ToInt32(ddlLookupValue.SelectedValue);
                    }

                }
            }

            condition.OperatorDisplayName = ddlOperators.SelectedItem.Text;
            condition.OperatorSchemaName = ddlOperators.SelectedValue;

            List<QueryCondition> lstConditions;
            if (Session["QueryConditions"] == null)
                lstConditions = new List<QueryCondition>();
            else
                lstConditions = (List<QueryCondition>)Session["QueryConditions"];

            lstConditions.Add(condition);
            Session["QueryConditions"] = lstConditions;

            grdConditions.DataSource = lstConditions;
            grdConditions.DataBind();

            ddlFields.SelectedIndex = 0;
            ddlOperators.Items.Clear();
            txtValue.Text = string.Empty;
            ddlLookupValue.Items.Clear();
            programmaticModalPopup_AlreadyRegistered.Hide();

        }

        private void HandleLookupField()
        {
            int fieldId = Convert.ToInt32(ddlFields.SelectedValue);
            if (fieldId == 0)
                return;

            List<EntityField> lstFields;

            if (Session["EntityFields"] != null)
                lstFields = (List<EntityField>)Session["EntityFields"];
            else
            {
                QueryBuilder queryBuilder = new QueryBuilder();
                lstFields = queryBuilder.GetEntityFields(Convert.ToInt32(ddlEntities.SelectedValue));
            }

            EntityField currentField = lstFields.Find(delegate(EntityField f) { return f.FieldId == fieldId; });

            int entityId = Convert.ToInt32(ddlEntities.SelectedValue);

            if (entityId == 9) // Account
            {

                if (currentField.Type.Id == 15) // lookup
                {
                    pnlLookupValue.Visible = true;
                    pnlStaticValue.Visible = false;
                    pnlDateValue.Visible = false;


                    lookupValidator.Enabled = true;
                    dateValidator.Enabled = false;
                    textValueValidator.Enabled = false;


                    QueryBuilder queryBuilder = new QueryBuilder();
                    List<Item> lstLookupValues = queryBuilder.GetCategoryItems(currentField.Category.Id);
                    if (lstLookupValues != null && lstLookupValues.Count > 0)
                    {
                        ddlLookupValue.DataSource = lstLookupValues;
                        ddlLookupValue.DataTextField = "Value";
                        ddlLookupValue.DataValueField = "Id";
                        ddlLookupValue.DataBind();
                    }

                }
                else if (currentField.Type.Id == 16) // bool
                {
                    pnlLookupValue.Visible = true;
                    pnlStaticValue.Visible = false;
                    pnlDateValue.Visible = false;

                    lookupValidator.Enabled = true;
                    dateValidator.Enabled = false;
                    textValueValidator.Enabled = false;


                    Item yes = new Item { Id = 1, Value = "نعم" };
                    Item no = new Item { Id = 0, Value = "لا" };

                    List<Item> lstItems = new List<Item> { yes, no };
                    ddlLookupValue.DataSource = lstItems;
                    ddlLookupValue.DataTextField = "Value";
                    ddlLookupValue.DataValueField = "Id";
                    ddlLookupValue.DataBind();

                }
                else if (currentField.Type.Id == 14) // Date
                {
                    pnlLookupValue.Visible = false;
                    pnlStaticValue.Visible = false;
                    pnlDateValue.Visible = true;

                    lookupValidator.Enabled = false;
                    dateValidator.Enabled = true;
                    textValueValidator.Enabled = false;
                }
                else
                {
                    pnlLookupValue.Visible = false;
                    pnlStaticValue.Visible = true;
                    pnlDateValue.Visible = false;

                    lookupValidator.Enabled = false;
                    dateValidator.Enabled = false;
                    textValueValidator.Enabled = true;
                }
            }
            else if (entityId == 11) // Appointment
            {
                ISS.IR.RRNMS.Library.BusinessLogic.Calendar PageCalendar = new Library.BusinessLogic.Calendar();

                switch (currentField.FieldSchemaName)
                {
                    case "AccountId":
                        ddlLookupValue.DataSource = PageCalendar.SelectListOfAllAccounts();
                        ddlLookupValue.DataTextField = "Value";
                        ddlLookupValue.DataValueField = "GUID";
                        ddlLookupValue.DataBind();
                        break;

                    case "SlotId":
                        ddlLookupValue.DataSource = PageCalendar.SelectListOfAllSlots();
                        ddlLookupValue.DataTextField = "Value";
                        ddlLookupValue.DataValueField = "GUID";
                        ddlLookupValue.DataBind();
                        break;
                    case "UserId":
                        ddlLookupValue.DataSource = PageCalendar.SelectListOfAllUsers();
                        ddlLookupValue.DataTextField = "Value";
                        ddlLookupValue.DataValueField = "GUID";
                        ddlLookupValue.DataBind();
                        break;

                    case "RoomId":
                        ddlLookupValue.DataSource = PageCalendar.SelectListOfAllRooms(5);
                        ddlLookupValue.DataTextField = "Value";
                        ddlLookupValue.DataValueField = "Id";
                        ddlLookupValue.DataBind();

                        break;

                    case "Date":
                        break;

                }

                if (currentField.FieldSchemaName == "Date")
                {
                    pnlLookupValue.Visible = false;
                    pnlStaticValue.Visible = false;
                    pnlDateValue.Visible = true;

                    lookupValidator.Enabled = false;
                    dateValidator.Enabled = true;
                    textValueValidator.Enabled = false;
                }
                else
                {
                    ddlLookupValue.Items.RemoveAt(0);

                    pnlLookupValue.Visible = true;
                    pnlStaticValue.Visible = false;
                    pnlDateValue.Visible = false;


                    lookupValidator.Enabled = true;
                    dateValidator.Enabled = false;
                    textValueValidator.Enabled = false;
                }


            }

            // If not String or Date or Numerci
            if (currentField.Type.Id != 12 && currentField.Type.Id != 13 && currentField.Type.Id != 14)
            {
                ddlLookupValue.Items.Insert(0, new ListItem("أختر ...", ""));
            }
        }

        private void BindSearchViews()
        {
            if (ddlEntities.SelectedValue == "" || Convert.ToInt32(ddlEntities.SelectedValue) == 0)
            {
                ddlSearchViews.Items.Clear();
                return;
            }
            QueryBuilder queryBuilder = new QueryBuilder();
            List<Item> lstSearchViews = queryBuilder.GetSearchViews(Convert.ToInt32(ddlEntities.SelectedValue));

            if (lstSearchViews != null)
            {
                ddlSearchViews.DataSource = lstSearchViews;
                ddlSearchViews.DataTextField = "Value";
                ddlSearchViews.DataValueField = "GUID";
                ddlSearchViews.DataBind();
            }
            else
                ddlSearchViews.Items.Clear();
        }

        #endregion

        protected void lnkLoadView_Click(object sender, EventArgs e)
        {
            if (ddlEntities.SelectedValue == "" || Convert.ToInt32(ddlEntities.SelectedValue) == 0)
                return;

            QueryBuilder queryBuilder = new QueryBuilder();

            Guid viewId = new Guid(ddlSearchViews.SelectedValue);

            List<QueryCondition> lstConditions = queryBuilder.GetQueryConditions(viewId);

            if (lstConditions != null)
            {
                Session["QueryConditions"] = lstConditions;
                grdConditions.DataSource = lstConditions;
                grdConditions.DataBind();

                Session["QueryConditions"] = lstConditions;
                btnSearch_Click(null, null);

                hiddenViewId.Value = viewId.ToString();
                txtViewName.Text = ddlSearchViews.SelectedItem.Text;
            }
        }

        protected void lnkSaveView_Click(object sender, EventArgs e)
        {
            if (hiddenViewId.Value == "")
            {
                // New View
                if (Session["QueryConditions"] != null)
                {
                    List<QueryCondition> lstConditions;
                    lstConditions = (List<QueryCondition>)Session["QueryConditions"];

                    QueryBuilder query = new QueryBuilder();
                    Guid viewId = query.CreateView(txtViewName.Text, Convert.ToInt32(ddlEntities.SelectedValue), lstConditions);
                    hiddenViewId.Value = viewId.ToString();

                    BindSearchViews();
                }

            }
            else
            {
                // View Already Saved ... Update View
                Guid viewId = new Guid(hiddenViewId.Value);

                if (ddlEntities.SelectedIndex != 0 && Session["QueryConditions"] != null)
                {
                    List<QueryCondition> lstConditions;
                    lstConditions = (List<QueryCondition>)Session["QueryConditions"];

                    QueryBuilder query = new QueryBuilder();
                    query.UpdateView(viewId, txtViewName.Text, lstConditions);

                    BindSearchViews();
                }
            }
        }


        protected void btnExportToExcel_Click(object sender, EventArgs e)
        {
            if (Session["dtSearchResult"] != null)
            {
                DataTable dtSearchResult = (DataTable)Session["dtSearchResult"];
                dtSearchResult.Columns.Remove("AccountId");
                ExportToExcel(dtSearchResult);
            }
        }

        /*
           private void ExportToExcel(DataTable dataTable)
           {
               try
               {
                   GridView gridView = new GridView();
                   gridView.DataSource = dataTable;

                   gridView.AutoGenerateColumns = true;
                   gridView.DataBind();

                   Response.Clear();

                   Response.AddHeader("content-disposition", "attachment; filename=\"RRNMS Query Builder Export on (" + DateTime.Now.Date.ToString("d MMM yyyy") + ").xls\"");

                   Response.Charset = "";

                   // If you want the option to open the Excel file without saving than

                   // comment out the line below

                   Response.Cache.SetCacheability(HttpCacheability.NoCache);

                   Response.ContentType = "application/vnd.xls";



                   System.IO.StringWriter stringWrite = new System.IO.StringWriter();

                   System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

                   gridView.RenderControl(htmlWrite);

                   Response.Write(stringWrite.ToString());

                   HttpContext.Current.ApplicationInstance.CompleteRequest();
               }
               catch (Exception exc)
               {
                   throw exc;
               }
           }
        
    
           private void ExportToExcel(DataTable dt)
           {
               try
               {
                   if (dt.Rows.Count > 0)
                   {
                       string attachment = "attachment; filename=\"RRNMS Query Builder Export on (" + DateTime.Now.Date.ToString("d MMM yyyy") + ").xls\"";
                       HttpContext context = HttpContext.Current;

                       context.Response.Clear();

                       foreach (DataColumn column in dt.Columns)
                           context.Response.Write(column.ColumnName + "\t");

                       context.Response.Write(Environment.NewLine);

                       foreach (DataRow row in dt.Rows)
                       {
                           for (int i = 0; i < dt.Columns.Count; i++)
                               context.Response.Write(row[i].ToString() + "\t");
                           context.Response.Write(Environment.NewLine);
                       }

                       context.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
                       context.Response.ContentType = "text/vnd.ms-excel";
                       context.Response.AppendHeader("Content-Disposition", attachment);
                       context.Response.End();
                   }
               }
               catch (Exception exc)
               { 
               }
           }
            */

        private void ExportToExcel(DataTable dt)
        {
            Response.Clear();
            Response.Buffer = true;

            Response.AddHeader("content-disposition",
             "attachment;filename=\"RRNMS Query Builder Export on (" + DateTime.Now.Date.ToString("d MMM yyyy") + ").xls\"");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            GridView GridView1 = new GridView();
            GridView1.DataSource = dt;
            GridView1.AllowPaging = false;
            GridView1.DataBind();

            //Change the Header Row back to white color
            GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");

            //Apply style to Individual Cells
            for (int l = 0; l < GridView1.HeaderRow.Cells.Count; l++)
                GridView1.HeaderRow.Cells[l].Style.Add("background-color", "blue");


            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridViewRow row = GridView1.Rows[i];

                //Change Color back to white
                row.BackColor = System.Drawing.Color.White;

                //Apply text style to each Row
                row.Attributes.Add("class", "textmode");

                //Apply style to Individual Cells of Alternating Row
                if (i % 2 != 0)
                {
                    for (int l = 0; l < row.Cells.Count; l++)
                        row.Cells[l].Style.Add("background-color", "#C2D69B");

                }
            }
            GridView1.RenderControl(hw);

            //style to format numbers to string
            //string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.BinaryWrite(System.Text.Encoding.UTF8.GetPreamble());

            //Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }

        public override void VerifyRenderingInServerForm(Control control)
        { }
    }
}